1 package org.thema.scriptware.dao;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.ResultSetMetaData;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 import java.text.SimpleDateFormat;
9 import java.util.ArrayList;
10 import java.util.Collection;
11 import java.util.HashMap;
12 import java.util.List;
13 import java.util.Map;
14 import oracle.jdbc.OracleConnection;
15 import oracle.jdbc.OracleDatabaseMetaData;
16
17 import org.thema.Util;
18
19 import oracle.jdbc.OracleResultSetMetaData;
20 import oracle.jdbc.driver.OracleResultSet;
21
22 /***
23 * Implementação do DAO de Oracle para o DmlDAO
24 * <br>
25 * Classe que contém todos os códigos específicos Oracle para
26 * a implementação da interface DmlDAO
27 * <br>
28 * O cliente fica assim resguardado de conhecer detalhes específicos de
29 * implementação para cada banco de dados (encapsulamento)
30 *
31 * @author Eduardo M. Sasso
32 * @since Jan 12, 2004
33 */
34 public class OracleDmlDAO implements DmlDAO {
35
36 private static final String ORACLE_DATE_FORMAT = "'dd/mm/yyyy hh24:mi:ss'";
37 private static final String JAVA_DATE_FORMAT = "dd/MM/yyyy HH:mm:ss";
38 private Connection conn;
39
40 public OracleDmlDAO(Connection conn) {
41 this.conn = conn;
42 }
43
44 public Collection getInsertScript(String table) throws SQLException {
45 return getInsertScript(table, "1=1");
46 }
47
48 private ResultSet getResultSet(String sql) throws SQLException {
49
50 String ident = " ";
51 Statement stmt = conn.createStatement();
52 ResultSet rs = stmt.executeQuery(sql);
53 return rs;
54 }
55
56 private ResultSet getPrimaryKeys(String table) throws SQLException {
57 OracleDatabaseMetaData metadata = new OracleDatabaseMetaData(((OracleConnection)conn));
58 ResultSet rs = metadata.getPrimaryKeys(null,null, table);
59 return rs;
60 }
61
62
63 public Collection getUpdateScript(String table, String whereClause) throws SQLException {
64 return null;
65 }
66
67 private Map getColumns(ResultSetMetaData rsmd) throws SQLException {
68 HashMap cols = new HashMap();
69 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
70 rsmd.getColumnName(i);
71
72
73
74
75 }
76 return null;
77 }
78
79 public Collection getInsertScript(String table, String whereClause) throws SQLException {
80 String sql = "select * from " + table + " where " + whereClause;
81 return getInsertScriptSQL(sql);
82 }
83
84 public Collection getInsertScriptSQL(String sql) throws SQLException {
85 ArrayList arrayList = new ArrayList();
86
87
88 String ident = " ";
89 try {
90 OracleResultSet rs = (OracleResultSet) getResultSet(sql);
91 OracleResultSetMetaData rsmd =
92 (OracleResultSetMetaData) rs.getMetaData();
93
94 String table= Util.getTableName(sql);
95
96
97
98
99 StringBuffer sbCols = new StringBuffer();
100 sbCols.append("(\n");
101 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
102 sbCols.append(ident + rsmd.getColumnName(i));
103 if (i < rsmd.getColumnCount()) {
104 sbCols.append(",\n");
105 }
106 }
107 sbCols.append(")\n");
108 String colunas = sbCols.toString();
109
110 StringBuffer sbInsert = new StringBuffer();
111 sbInsert.append("\ninsert into ");
112 sbInsert.append(table);
113 sbInsert.append(" ");
114 sbInsert.append(colunas);
115 sbInsert.append("values");
116
117
118 while (rs.next()) {
119 StringBuffer sbValues = new StringBuffer();
120
121 sbValues.append("(\n");
122 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
123 if (rs.getString(i) != null) {
124 switch (rsmd.getColumnType(i)) {
125 case oracle.jdbc.OracleTypes.NUMBER :
126 {
127 sbValues.append(ident + rs.getInt(i));
128 break;
129 }
130 case oracle.jdbc.OracleTypes.VARCHAR :
131 {
132 sbValues.append(ident + "'");
133
134
135
136
137 sbValues.append(
138 Util.replace(
139 rs.getString(i),
140 "'",
141 "''",
142 true));
143 sbValues.append("'");
144 break;
145 }
146 case oracle.jdbc.OracleTypes.CHAR :
147 {
148 sbValues.append(ident + "'");
149
150
151
152
153 sbValues.append(
154 Util.replace(
155 rs.getString(i),
156 "'",
157 "''",
158 true));
159 sbValues.append("'");
160 break;
161 }
162 case oracle.jdbc.OracleTypes.DATE :
163 {
164 SimpleDateFormat campoDate =
165 new SimpleDateFormat(JAVA_DATE_FORMAT);
166 sbValues.append(ident + "to_date('");
167 sbValues.append(
168 campoDate.format(rs.getDate(i)));
169 sbValues.append("',");
170 sbValues.append(ORACLE_DATE_FORMAT);
171 sbValues.append(")");
172 break;
173 }
174 default :
175 {
176 throw new SQLException("Tipo de Dados não suportado! " + rsmd.getColumnTypeName(i) );
177 }
178 }
179 } else {
180 sbValues.append(ident + rs.getString(i));
181 }
182
183 if (i < rsmd.getColumnCount()) {
184 sbValues.append(",\n");
185 }
186 }
187 sbValues.append(");");
188
189 StringBuffer sbScript = new StringBuffer();
190 sbScript.append(sbInsert.toString());
191 sbScript.append(sbValues.toString());
192
193 arrayList.add(sbScript.toString());
194
195 }
196
197 } catch (SQLException e) {
198 e.printStackTrace(System.out);
199 }
200 return arrayList;
201 }
202
203 }